高性能MySQL读书笔记 (五)

1. 优化服务器设置

1. MySQL有大量的可以修改的参数,但不应该随便修改.应该将更多时间花在schema的优化,索引,查询设计上
2. 配置文件路径: 通常在/etc/my.cnf
3. 不建议动态修改变量,因为可能导致意外的副作用
4. 通过基准测试迭代优化
5. 具体配置项设置请参照官网手册,这里只提及部分

1.1 配置内存使用

1. 确定可使用内存上限
2. 每个连接使用多少内存,如排序缓冲和临时表
3. 确定操作系统内存使用量
4. 把剩下的分配给缓存,如InnoDB缓存池

1.2 配置MySQL的I/O行为

1. 有些配置项影响如何同步数据到磁盘及如何恢复操作,这对性能影响很大,而且表现了性能和数据安全之间的平衡

1.2.1 InnoDB I/O配置

1. 重要配置: InnoDB日志文件大小,InnoDB怎样刷新日志缓冲,InnoDB怎样执行I/O
2. InnoDB使用日志减少提交事务时开销,不用每个事务提交时把缓冲池的脏块刷到磁盘中
3. 事务日志可以把随机IO变成顺序IO,同时如果发生断电,InnoDB可以重放日志恢复已经提交的事务
4. sync_binlog选项控制MySQL怎么刷新二进制日志到磁盘
5. 把二进制日志放到一个带有电池保护的写缓存的RAID卷可以极大的提升性能

1.2.2 MyISAM的I/O配置

1. 因为MyISAM表每次写入都会将索引变更刷新到磁盘
2. 批量操作时,通过设置delay_key_write可以延迟索引写入,可以提升性能
3. 配置MyISAM怎样尝试从损坏中恢复

1.3 配置MySQL并发

1.3.1 InnoDB并发配置

1. 如果在InnoDB并发方面有问题,解决方案通常是升级服务器
2. innodb_thread_concurrency: 限制一次性可以有多少线程进入内核(根据实践取合适值)
3. innodb_thread_sleep_delay: 线程第一次进入内核失败等的时间,如果还不能进入则放入等待线程队列
4. innodb_commit_concurrency: 控制有多少线程可以在同一时间提交
5. 使用线程池限制并发: MariaDB已经实现

1.3.2 MyISAM并发配置

1. concurrency_insert: 配置MyISAM打开并发插入

1.4 其他

1. 基于工作负载的配置: 利用工具分析并调整配置
2. max_connections: 保证服务器不会因应用程序激增的连接而不堪重负
3. 安全和稳定的设置: 感兴趣者请自行google
4. 高级InnoDB设置: 感兴趣者请自行google
5. InnoDB两个重要配置: innodb_buffer_pool_size和innodb_log_file_size

2. 复制

MySQL内建的复制功能是构建基于MySQL的大规模,高性能应用的基础.同时也是高可用性,可扩展性,灾难恢复,备份及数据仓库等工作的基础

2.1 概述

1. 解决问题: 让一台服务器的数据与其他服务器保持同步.主库可以同步到多台备库,备库本身也可以配置为另一台服务器的主库
2. 复制原理: 通过在主库上记录二进制日志,在备库重放日志的方式实现异步的数据复制
3. 复制方式: 基于行的复制和基于语句的复制
4. 向后兼容: 新版本只能作为老版本的备库,反之不行

2.2 用途

1. 数据分布: 在不同地理位置分布数据备份,可以随意停止或开始复制.基于行比基于语句带宽压力更大
2. 负载均衡: 将读操作分布到多个服务器上
3. 备份: 复制是备份的一项有意义的技术补充
4. 高可用性和故障切换: 避免单点失败
5. MySQL升级测试: 一种普遍做法是使用一个更高版本的MySQL作为备库保证实例升级前查询能够在备库按照预期执行

2.3 过程

1. 主库把数据更改记录到二进制日志(Binary Log)
2. 备库将主库上的日志复制到自己的中继日志(Relay Log)
3. 备库读取中继日志中的事件,将其重放到备库数据上
4. 局限: 主库上并发运行的查询在备库只能串行化执行,因为只有一个sql线程重放中继日志事件,这是很多工作负载的性能瓶颈

2.4 复制配置

1. 在每台服务器上创建复制账号: 需要REPLICATION SLAVE权限
2. 配置主库和备库: 每个服务器的ID需要唯一不能冲突
3. 通知备库连接到主库并从主库复制数据
4. CHANGE MASTER TO: 指定备库连接的主库设置
5. SHOW SLAVE STATUS: 检查复制是否正确执行
6. START SLAVE: 开始复制
7. SHOW PROCESSLIST: 查看复制线程,IO线程(发送或获取日志),SQL线程(重放日志)
8. 推荐配置: 开启sync_binlog

2.5 从另一个服务器开始复制

问题: 主库已经运行一段时间,用一台新安装的备库与之同步
保持同步条件:

1. 某个时间点的主库的数据快照
2. 主库当前的二进制日志文件,和获得数据快照时在该二进制日志文件中的偏移量.通过这两个可以确定二进制日志的位置
3. 从快照时间到现在的二进制日志

克隆备库方法:

1. 冷备份: 关闭主库,复制数据.主库重启后会使用新的二进制文件,在备库指向这个文件的起始处
2. 热备份:如果只有MyISAM,可以通过mysqlhotcopy或rsync来复制数据
3. 如果只包含InnoDB: 可以使用mysqldump转储主库数据并加载到备库,然后设置相应的二进制日志坐标
4. 使用快照或备份: 使用主库的快照或者备份初始化备库,然后指定二进制日志坐标
5. 使用Percona Xtrabackup: 备份时不阻塞服务器操作,可以在不影响主库情况下设置备库
6. 使用另外的备库: 实质就是把另外的备库当成主库进行数据克隆

2.6 复制的原理

2.6.1 基于语句的复制

1. 主库会记录那些造成数据更改的查询
2. MySQL5.0之前只支持基于语句的复制
3. 对于函数,存储过程和触发器在基于语句的复制模式可能存在问题
4. 更新必须是串行,需要更多的锁

2.6.2 基于行的复制

1. 将实际的数据记录在二进制日志
2. 能够更高效复制数据
3. 基于行的复制事件格式,对人不可读,可以使用mysqlbinlog
4. 很难进行时间点恢复
5. 有些操作,如全表更新(update)复制开销会很大

2.7 复制拓扑

2.7.1 基本原则

1. 一个MySQL备库实例只能有一个主库
2. 每个备库必须有一个唯一的服务器id
3. 一个主库可以有多个备库
4. 如果打开log_slave_update一个备库可以把其主库上的数据变化传播到其他备库

2.7.2 一主多备

1. 适用于少量写和大量读,可以把读分摊到多个备库上
2. 当作待用的主库
3. 放到远程数据中心,用作灾难恢复
4. 作为备份,培训,开发或测试服务器

2.7.3 双主复制

1. 个数据库互为主库和备库
2. 容易造成数据不同步
3. 通常并不建议使用这种模式

2.7.4 主动被动的双主模式

1. 类似双主复制,把其中一台配置为只读
2. 类似于创建一个热备份
3. 可以用作执行读操作,备份,离线维护及升级

2.7.5 有备库的双主模式

1. 双主模式下,各自有备库

2.7.6 主库,分发主库和备库

1. 问题: 备库足够多时会对主库造成很大的负载
2. 方案: 将其中部分备库当成主库,分发给更多的备库
3. 通过分发主库,可以对二进制日志事件执行过滤和重写规则

2.8 复制管理和维护

1. 监控复制: SHOW MASTER STATUS查看主库状态, SHOW BINLOG EVENTS查看复制事件
2. 测量备库延迟: 可以使用Percona Toolkit里的pt-hearbeat
3. 确定主备是否一致
4. 备库换主库: 难点在于获取新主库合适的二进制日志位置
5. 备库提升为主库分为计划内提升和计划外提升

2.8.1 计划内提升

1. 停止向老的主库写入
2. 备库赶上主库
3. 备库设置为主库
4. 将备库和写操作指向新主库,然后开启主库的写入

2.8.2 计划外提升

当主库崩溃时,需要提升一台备库替代

1. 确定最新的备库
2. 让所有备库执行完从崩溃前主库获得的中继日志,如果未完成则更换主库,会丢失原先的日志事件
3. 重新完成主备的配置

2.9 复制的问题和解决方案

2.9.1 数据损坏或丢失

1. 主库意外关闭: 主库开启sync_binlog避免事件丢失,使用Percona Toolkit中的pt-table-checksum检查主备一致性
2. 备库意外关闭: 重启后观察MySQL错误日志,想方法获取备库指向主库的日志偏移量
3. 主库上的二进制日志损坏: 跳过所有损坏的事件,手动找到一个完好的事件开始
4. 备库上的中继日志损坏: MySQL5.5后能在崩溃后自动重新获取中继日志
5. 二进制日志于InnoDB事务日志不同步: 除非备库中继日志有保存,否则自求多福

2.9.2 其他

1. 如果使用myisam,在关闭Mysql前需要确保已经运行了stop slave,否则在服务器关闭时会kill所有正在运行的查询.
2. 如果是事务型,失败的更新会在主库上回滚而且不会记录到二进制日志
3. 避免混用事务和非事务: 如果备库发生死锁而主库没有,事务型会回滚而非事务型则不会造成不同步
4. 主库和备库使用不同存储引擎容易导致问题
5. 不唯一和未定义备库服务器id
6. 避免在主库上创建备库上没有的表,因为复制可能中断
7. 基于语句复制时,主库上没有安全使用临时表的方法.丢失临时表: 备库崩溃时,任何复制线程拥有的临时表都会丢失,重启备库后所有依赖临时表的语句都会失败
8. InnoDB加锁读引起的锁争用: 将大命令拆成小命令可以有效减少锁竞争
9. 过大的复制延迟: 定位执行慢的语句,改善机器配置
10. 其他: 查看官网手册

2.10 复制高级特性

1. 半同步复制: 当提交事务,客户端收到查询结束反馈前必须保证二进制日志已经传输到至少一台备库上,主库将事务提交到磁盘上之后会增加一些延迟
2. 复制心跳: 保证备库一直与主库相联系,如果出现断开的网络连接,备库会注意到丢失的心跳数据

2.11 其他复制技术

1. Percona XtraDB Cluster的同步复制
2. Tungsten